--drop table if exists navinfo_stage_0200;

--创建外部表
create external table if not exists dongfeng.navinfo_stage_0200 (
        alarm                                             BIGINT              COMMENT '报警标志位',
        status                                            BIGINT              COMMENT '状态标志位',
        longitude                                         INT                 COMMENT '经度',
        latitude                                          INT                 COMMENT '纬度',
        originalLng                                       INT                 COMMENT '经度WGS84',
        originalLat                                       INT                 COMMENT '纬度WGS84',
        height                                            INT                 COMMENT '高程单位为米',
        speed                                             INT                 COMMENT '速度km/h',
        direction                                         INT                 COMMENT '方向0~359正北为0顺时针',
        gpsDate                                           BIGINT              COMMENT 'gps时间',
        mileage                                           BIGINT              COMMENT '里程单位为米',
        receiveDate                                       BIGINT              COMMENT '接收时间',
        isPatch                                           BOOLEAN             COMMENT '是否补偿数据',
        oil                                               INT                 COMMENT '油量1/10L',
        recorderSpeed                                     INT                 COMMENT '行驶记录仪速度km/h',
        speedAddition                                     STRING              COMMENT '超速报警附加信息',
        areaAddition                                      STRING              COMMENT '进入区域路线报警附加信息',
        routeAddition                                     STRING              COMMENT '路段行驶时间不足/过长信息类',
        temAlarm                                          STRING              COMMENT '温度报警附加信息',
        starStatus                                        INT                 COMMENT '星况1正常0不正常默认为1',
        starNumber                                        INT                 COMMENT '星数默认为1',
        alarmIdentify                                     STRING              COMMENT '需要人工确认的报警ID',
        signalStatus                                      INT                 COMMENT '车辆信号状态',
        ioStatus                                          INT                 COMMENT 'IO状态位',
        analogAD0                                         INT                 COMMENT 'AD0模拟量',
        analogAD1                                         INT                 COMMENT 'AD1模拟量',
        signalStrength                                    INT                 COMMENT '无线通信网络信号强度',
        satelliteNumber                                   INT                 COMMENT 'GNSS定位卫星数',
        isValid                                           BOOLEAN             COMMENT '数据是否有效',
        defenceAddition                                   STRING              COMMENT '关键点围栏附加信息',
        parkingAddition                                   STRING              COMMENT '区域滞留超时附加信息',
        statusAddition                                    STRING              COMMENT '车辆状态附加信息',
        breakdownAddition                                 STRING              COMMENT '车辆故障附加信息',
        additionAlarm                                     STRING              COMMENT '附加报警位',
        analysisData_turningAngle                         INT                 COMMENT '发生急转弯时的当前角度值',
        analysisData_lowOilDrivingValue                   INT                 COMMENT '发生低油量行驶报警时的油量值',
        analysisData_vehicleSpeedFromEcu                  INT                 COMMENT '获取到ECU传的车辆速度',
        analysisData_rpmWhenAlarming                      INT                 COMMENT '发生报警时的发动机转速当前值',
        analysisData_velocityChangeValue                  INT                 COMMENT '发生急加速、急减速的当前值',
        analysisData_currentGearshift                     INT                 COMMENT '车辆当前档位',
        analysisData_currentRPM                           INT                 COMMENT '车辆当前发送机转速',
        analysisData_deviceIdentity                       STRING              COMMENT '当车辆ACC开启时终端查询并存储的北斗终端ID号',
        analysisData_brakeTimes                           INT                 COMMENT '刹车区间次数',
        analysisData_clutchTimes                          INT                 COMMENT '离合区间次数',
        analysisData_retarderTimes                        INT                 COMMENT '缓速器次数',
        analysisData_absTimes                             INT                 COMMENT 'ABS次数',
        analysisData_reverseTimes                         INT                 COMMENT '倒档次数',
        batteryPower                                      INT                 COMMENT '电池电量百分比',
        moduleVoltages                                    STRING              COMMENT '模块电压',
        electricVehicle                                   BIGINT              COMMENT '电车状态',
        batteryInfo_motorTemperature                      INT                 COMMENT '电机温度单位摄氏度',
        batteryInfo_motorControlTemp                      INT                 COMMENT '电机控制器温度单位摄氏度',
        batteryInfo_mcuFaultCode                          INT                 COMMENT 'MCU系统故障代码',
        batteryInfo_bmsStatus                             INT                 COMMENT 'BMS基本状态0正常01BSM故障无法工作',
        batteryInfo_batteryAveTem                         INT                 COMMENT '电池组平均温度单位摄氏度',
        batteryInfo_batteryCurrent                        STRING              COMMENT '电池组充放电电流单位A',
        batteryInfo_totalBatteryV                         STRING              COMMENT '电池组总电压单位V',
        batteryInfo_batteryFaultCode                      INT                 COMMENT '电池组系统故障信息',
        batteryInfo_batteryMaxTem                         INT                 COMMENT '电池组允许最高温度单位摄氏度',
        batteryInfo_batteryMinTem                         INT                 COMMENT '电池组允许最低温度单位摄氏度',
        batteryInfo_batteryMinSoc                         INT                 COMMENT '电池组允许最低使用SOC%',
        batteryInfo_maxChargingV                          STRING              COMMENT '最高允许充电端电压V',
        batteryInfo_maxChargingE                          STRING              COMMENT '最高允许充电电流充电电流为负',
        batteryInfo_totalStatus                           INT                 COMMENT '整车状态0WAIT1READY',
        batteryInfo_vehicleSpeed                          STRING              COMMENT '车辆速度km/h',
        batteryInfo_shiftStatus                           INT                 COMMENT '档位状态-2前进档D-1倒档R0空档N1前进档D1到12前进档D12',
        batteryInfo_vcuFaultCode                          INT                 COMMENT 'VCU系统故障码',
        batteryInfo_enduranceMileage                      INT                 COMMENT '续航里程单位米',
        batteryInfo_acceleratorPedal                      STRING              COMMENT '油门踏板状态%',
        batteryInfo_brakePedal                            STRING              COMMENT '制动踏板状态%',
        batteryInfo_modelInfo                             INT                 COMMENT '整车模式信息',
        batteryInfo_switchInfo                            INT                 COMMENT '整车部分开关量信息',
        batteryInfo_electricAttachment                    INT                 COMMENT '电附件使能',
        batteryInfo_motorRpm                              INT                 COMMENT '驱动电机转速rpm',
        alarmFilter                                       BIGINT              COMMENT '报警撤销状态',
        standardMileage                                   STRING              COMMENT '标准里程km保留2位小数',
        standardFuelCon                                   STRING              COMMENT '标准油耗L保留2位小数',
        staytimeParkingAddition                           STRING              COMMENT '',
        ecuDate                                           BIGINT              COMMENT 'ECU时间',
        temperatureAddition_vehType                       INT                 COMMENT '车型定义',
        temperatureAddition_tyrePosition                  INT                 COMMENT '轮胎位置',
        temperatureAddition_tyreCondition                 INT                 COMMENT '轮胎状态',
        temperatureAddition_tyrePressure                  INT                 COMMENT '胎压',
        temperatureAddition_tyreTemperature               INT                 COMMENT '胎温',
        temperatureAddition_temAlarmThreshold             INT                 COMMENT '温度报警阈值',
        temperatureAddition_tyreHalarmThreshold           INT                 COMMENT '胎压高报警阈值',
        temperatureAddition_tyreLalarmThreshold           INT                 COMMENT '胎压低报警阈值',
        temperatureAddition_tyreNomimalValue              INT                 COMMENT '标称胎压',
        cancleBreakdownAddition                           STRING              COMMENT '消失故障码',
        terminalId                                        BIGINT              COMMENT '终端号',
        stayTime                                          BIGINT              COMMENT '停留时间',
        serialNumber                                      INT                 COMMENT '流水号',
        carLockEffect_engineSpeed                         INT                 COMMENT '发动机转速',
        carLockEffect_actualOutputTorque                  INT                 COMMENT '发动机实际输出扭矩百分比',
        carLockEffect_throttleOpening                     INT                 COMMENT '油门开度',
        gearStatus                                        INT                 COMMENT '设置on档状态',
        tcuLoad                                           BIGINT              COMMENT 'TCU载重kg',
        vecuLoad                                          BIGINT              COMMENT 'VECU载重kg',
        ureaInjectionAvg                                  INT                 COMMENT '尿素喷射量平均值g/h',
        ureaPressureAvg                                   INT                 COMMENT '尿素压力平均值kpa',
        transmissionOilTemperature                        double              COMMENT '变速箱油温单位:',
        tcuRamp                                           INT                 COMMENT '坡道（TCU）单位：%',
        engineRunningTime                                 double              COMMENT '发动机运行时间 单位 hr',
        dpfAshLoading                                     INT                 COMMENT 'DPF 灰载量 单位：%',
        carbonLoad                                        INT                 COMMENT '碳载量 单位：%',
        totalureaInjection                                BIGINT              COMMENT '尿素喷射量（总消耗量） 单位：g',
        ureaLevel                                         INT                 COMMENT '尿素液位 单位：%',
        newMeterMileage                                   DOUBLE              COMMENT '换仪表后里程 单位：km',
        cargoBoxTemperature1                              int              comment '货箱温度1（厢前）    ',
        cargoBoxTemperature2                              int              comment '货箱温度2（厢中）    ',
        cargoBoxTemperature3                              int              comment '货箱温度3（厢后）    ',
        cargoBoxTemperature4                              int              comment '货箱温度4（厢后回风）',
        containerHumidity1                                int               comment '货箱湿度1（厢前）    ',
        containerHumidity2                                int               comment '货箱湿度2（厢中）    ',
        containerHumidity3                                int               comment '货箱湿度3（厢后）    ',
        containerHumidity4                                int               comment '货箱湿度4（厢后回风）',
        cargoBoxDoorStatus1                               int               comment '货箱车门状态1（左侧）',
        cargoBoxDoorStatus2                               int               comment '货箱车门状态2（右侧）',
        cargoBoxDoorStatus3                               int               comment '货箱车门状态3（后门）',
        switchStatus                                      int               commnet '冷机开关状态0关1开',
        ureaTemperature                                   INT                 COMMENT '尿素温度 单位：摄氏度',
        gasBox                                            STRING              COMMENT '气耗相关信息',
        part_time                                         INT                 COMMENT '分区时间',
        hashtid                                           INT
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '${data_path}/stage_import_0200';

--drop table if exists navinfo_parquet_0200;

--创建目标表
create table if not exists dongfeng.navinfo_parquet_0200(
        alarm                                             BIGINT              COMMENT '报警标志位',
        status                                            BIGINT              COMMENT '状态标志位',
        longitude                                         INT                 COMMENT '经度',
        latitude                                          INT                 COMMENT '纬度',
        originalLng                                       INT                 COMMENT '经度WGS84',
        originalLat                                       INT                 COMMENT '纬度WGS84',
        height                                            INT                 COMMENT '高程单位为米',
        speed                                             INT                 COMMENT '速度km/h',
        direction                                         INT                 COMMENT '方向0~359正北为0顺时针',
        gpsDate                                           BIGINT              COMMENT 'gps时间',
        mileage                                           BIGINT              COMMENT '里程单位为米',
        receiveDate                                       BIGINT              COMMENT '接收时间',
        isPatch                                           BOOLEAN             COMMENT '是否补偿数据',
        oil                                               INT                 COMMENT '油量1/10L',
        recorderSpeed                                     INT                 COMMENT '行驶记录仪速度km/h',
        speedAddition                                     STRING              COMMENT '超速报警附加信息',
        areaAddition                                      STRING              COMMENT '进入区域路线报警附加信息',
        routeAddition                                     STRING              COMMENT '路段行驶时间不足/过长信息类',
        temAlarm                                          STRING              COMMENT '温度报警附加信息',
        starStatus                                        INT                 COMMENT '星况1正常0不正常默认为1',
        starNumber                                        INT                 COMMENT '星数默认为1',
        alarmIdentify                                     STRING              COMMENT '需要人工确认的报警ID',
        signalStatus                                      INT                 COMMENT '车辆信号状态',
        ioStatus                                          INT                 COMMENT 'IO状态位',
        analogAD0                                         INT                 COMMENT 'AD0模拟量',
        analogAD1                                         INT                 COMMENT 'AD1模拟量',
        signalStrength                                    INT                 COMMENT '无线通信网络信号强度',
        satelliteNumber                                   INT                 COMMENT 'GNSS定位卫星数',
        isValid                                           BOOLEAN             COMMENT '数据是否有效',
        defenceAddition                                   STRING              COMMENT '关键点围栏附加信息',
        parkingAddition                                   STRING              COMMENT '区域滞留超时附加信息',
        statusAddition                                    STRING              COMMENT '车辆状态附加信息',
        breakdownAddition                                 STRING              COMMENT '车辆故障附加信息',
        additionAlarm                                     STRING              COMMENT '附加报警位',
        analysisData_turningAngle                         INT                 COMMENT '发生急转弯时的当前角度值',
        analysisData_lowOilDrivingValue                   INT                 COMMENT '发生低油量行驶报警时的油量值',
        analysisData_vehicleSpeedFromEcu                  INT                 COMMENT '获取到ECU传的车辆速度',
        analysisData_rpmWhenAlarming                      INT                 COMMENT '发生报警时的发动机转速当前值',
        analysisData_velocityChangeValue                  INT                 COMMENT '发生急加速、急减速的当前值',
        analysisData_currentGearshift                     INT                 COMMENT '车辆当前档位',
        analysisData_currentRPM                           INT                 COMMENT '车辆当前发送机转速',
        analysisData_deviceIdentity                       STRING              COMMENT '当车辆ACC开启时终端查询并存储的北斗终端ID号',
        analysisData_brakeTimes                           INT                 COMMENT '刹车区间次数',
        analysisData_clutchTimes                          INT                 COMMENT '离合区间次数',
        analysisData_retarderTimes                        INT                 COMMENT '缓速器次数',
        analysisData_absTimes                             INT                 COMMENT 'ABS次数',
        analysisData_reverseTimes                         INT                 COMMENT '倒档次数',
        batteryPower                                      INT                 COMMENT '电池电量百分比',
        moduleVoltages                                    STRING              COMMENT '模块电压',
        electricVehicle                                   BIGINT              COMMENT '电车状态',
        batteryInfo_motorTemperature                      INT                 COMMENT '电机温度单位摄氏度',
        batteryInfo_motorControlTemp                      INT                 COMMENT '电机控制器温度单位摄氏度',
        batteryInfo_mcuFaultCode                          INT                 COMMENT 'MCU系统故障代码',
        batteryInfo_bmsStatus                             INT                 COMMENT 'BMS基本状态0正常01BSM故障无法工作',
        batteryInfo_batteryAveTem                         INT                 COMMENT '电池组平均温度单位摄氏度',
        batteryInfo_batteryCurrent                        STRING              COMMENT '电池组充放电电流单位A',
        batteryInfo_totalBatteryV                         STRING              COMMENT '电池组总电压单位V',
        batteryInfo_batteryFaultCode                      INT                 COMMENT '电池组系统故障信息',
        batteryInfo_batteryMaxTem                         INT                 COMMENT '电池组允许最高温度单位摄氏度',
        batteryInfo_batteryMinTem                         INT                 COMMENT '电池组允许最低温度单位摄氏度',
        batteryInfo_batteryMinSoc                         INT                 COMMENT '电池组允许最低使用SOC%',
        batteryInfo_maxChargingV                          STRING              COMMENT '最高允许充电端电压V',
        batteryInfo_maxChargingE                          STRING              COMMENT '最高允许充电电流充电电流为负',
        batteryInfo_totalStatus                           INT                 COMMENT '整车状态0WAIT1READY',
        batteryInfo_vehicleSpeed                          STRING              COMMENT '车辆速度km/h',
        batteryInfo_shiftStatus                           INT                 COMMENT '档位状态-2前进档D-1倒档R0空档N1前进档D1到12前进档D12',
        batteryInfo_vcuFaultCode                          INT                 COMMENT 'VCU系统故障码',
        batteryInfo_enduranceMileage                      INT                 COMMENT '续航里程单位米',
        batteryInfo_acceleratorPedal                      STRING              COMMENT '油门踏板状态%',
        batteryInfo_brakePedal                            STRING              COMMENT '制动踏板状态%',
        batteryInfo_modelInfo                             INT                 COMMENT '整车模式信息',
        batteryInfo_switchInfo                            INT                 COMMENT '整车部分开关量信息',
        batteryInfo_electricAttachment                    INT                 COMMENT '电附件使能',
        batteryInfo_motorRpm                              INT                 COMMENT '驱动电机转速rpm',
        alarmFilter                                       BIGINT              COMMENT '报警撤销状态',
        standardMileage                                   STRING              COMMENT '标准里程km保留2位小数',
        standardFuelCon                                   STRING              COMMENT '标准油耗L保留2位小数',
        staytimeParkingAddition                           STRING              COMMENT '',
        ecuDate                                           BIGINT              COMMENT 'ECU时间',
        temperatureAddition_vehType                       INT                 COMMENT '车型定义',
        temperatureAddition_tyrePosition                  INT                 COMMENT '轮胎位置',
        temperatureAddition_tyreCondition                 INT                 COMMENT '轮胎状态',
        temperatureAddition_tyrePressure                  INT                 COMMENT '胎压',
        temperatureAddition_tyreTemperature               INT                 COMMENT '胎温',
        temperatureAddition_temAlarmThreshold             INT                 COMMENT '温度报警阈值',
        temperatureAddition_tyreHalarmThreshold           INT                 COMMENT '胎压高报警阈值',
        temperatureAddition_tyreLalarmThreshold           INT                 COMMENT '胎压低报警阈值',
        temperatureAddition_tyreNomimalValue              INT                 COMMENT '标称胎压',
        cancleBreakdownAddition                           STRING              COMMENT '消失故障码',
        terminalId                                        BIGINT              COMMENT '终端号',
        stayTime                                          BIGINT              COMMENT '停留时间',
        serialNumber                                      INT                 COMMENT '流水号',
        carLockEffect_engineSpeed                         INT                 COMMENT '发动机转速',
        carLockEffect_actualOutputTorque                  INT                 COMMENT '发动机实际输出扭矩百分比',
        carLockEffect_throttleOpening                     INT                 COMMENT '油门开度',
        gearStatus                                        INT                 COMMENT '设置on档状态',
        tcuLoad                                           BIGINT              COMMENT 'TCU载重kg',
        vecuLoad                                          BIGINT              COMMENT 'VECU载重kg',
        ureaInjectionAvg                                  INT                 COMMENT '尿素喷射量平均值g/h',
        ureaPressureAvg                                   INT                 COMMENT '尿素压力平均值kpa',
        transmissionOilTemperature                        double              COMMENT '变速箱油温单位：',
        tcuRamp                                           INT                 COMMENT '坡道（TCU）单位：%',
        engineRunningTime                                 double              COMMENT '发动机运行时间 单位 hr',
        dpfAshLoading                                     INT                 COMMENT 'DPF 灰载量 单位：%',
        carbonLoad                                        INT                 COMMENT '碳载量 单位：%',
        totalureaInjection                                BIGINT              COMMENT '尿素喷射量（总消耗量） 单位：g',
        ureaLevel                                         INT                 COMMENT '尿素液位 单位：%',
        newMeterMileage                                   DOUBLE              COMMENT '换仪表后里程 单位：km',
        cargoBoxTemperature1                              int              comment '货箱温度1（厢前）    ',
        cargoBoxTemperature2                              int              comment '货箱温度2（厢中）    ',
        cargoBoxTemperature3                              int              comment '货箱温度3（厢后）    ',
        cargoBoxTemperature4                              int              comment '货箱温度4（厢后回风）',
        containerHumidity1                                int               comment '货箱湿度1（厢前）    ',
        containerHumidity2                                int               comment '货箱湿度2（厢中）    ',
        containerHumidity3                                int               comment '货箱湿度3（厢后）    ',
        containerHumidity4                                int               comment '货箱湿度4（厢后回风）',
        cargoBoxDoorStatus1                               int               comment '货箱车门状态1（左侧）',
        cargoBoxDoorStatus2                               int               comment '货箱车门状态2（右侧）',
        cargoBoxDoorStatus3                               int               comment '货箱车门状态3（后门）',
        switchStatus                                      int               commnet '冷机开关状态0关1开',
        ureaTemperature                                   INT                 COMMENT '尿素温度 单位：摄氏度',
        gasBox                                            STRING              COMMENT '气耗相关信息',
        create_time                                       STRING              COMMENT '数据创建时间'
) partitioned by (part_time int,hashtid int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
